import os
import numpy as np
from flask import Flask, request, jsonify, send_from_directory, session
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
from math import ceil
from flask import request
from sqlalchemy import text
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from flask_mysqldb import MySQL
from flask import make_response
app = Flask(__name__, static_url_path='', static_folder='frontend')

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost/cafeteria_ordering_system'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['UPLOAD_FOLDER'] = 'frontend/images/products'
app.secret_key = 'supersecretkey'
app.config['SECRET_KEY'] = 'secret_key_for_session'
db = SQLAlchemy(app)


# 用户模型
class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    phone_number = db.Column(db.String(15), unique=True, nullable=False)
    password = db.Column(db.String(255), nullable=False)
    role = db.Column(db.Enum('admin', 'consumer'), nullable=False)

# 窗口模型
class Window(db.Model):
    __tablename__ = 'windows'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    dishes = db.relationship('Product', backref='window', lazy=True)

# 菜品模型
class Product(db.Model):
    __tablename__ = 'products'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    picture_path = db.Column(db.String(255))
    price = db.Column(db.Numeric(10, 2), nullable=False)
    stock = db.Column(db.Integer, nullable=False)
    window_id = db.Column(db.Integer, db.ForeignKey('windows.id'), nullable=False)

# 购物车模型
class Cart(db.Model):
    __tablename__ = 'carts'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    products_id = db.Column(db.Integer, db.ForeignKey('products.id'))
    quantity = db.Column(db.Integer, nullable=False)
    user = db.relationship('User', backref=db.backref('carts', lazy=True))
    product = db.relationship('Product', backref=db.backref('carts', lazy=True))

# 订单模型
class Orders(db.Model):
    __tablename__ = 'orders'
    id = db.Column(db.Integer, primary_key=True)
    consumer_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    total_price = db.Column(db.Numeric(10, 2), nullable=False)
    status = db.Column(db.Enum('pending', 'completed', 'cancelled'), nullable=False, default='pending')
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())
    updated_at = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp())
    consumer = db.relationship('User', backref=db.backref('orders', lazy=True))

# 订单详情模型
class OrderItem(db.Model):
    __tablename__ = 'order_items'
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('orders.id'))
    products_id = db.Column(db.Integer, db.ForeignKey('products.id'))
    quantity = db.Column(db.Integer, nullable=False)
    price = db.Column(db.Numeric(10, 2), nullable=False)
    window_id = db.Column(db.Integer, db.ForeignKey('windows.id'))  # 确保这里有 window_id

    order = db.relationship('Orders', backref=db.backref('order_items', lazy=True))
    product = db.relationship('Product', backref=db.backref('order_items', lazy=True))
    window = db.relationship('Window', backref=db.backref('order_items', lazy=True))

class Feedback(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    consumer_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    order_id = db.Column(db.Integer, db.ForeignKey('orders.id'), nullable=False)
    rating = db.Column(db.Integer, nullable=False)  # 评分（1-5）
    comment = db.Column(db.Text)
    created_at = db.Column(db.TIMESTAMP, server_default=db.func.now())
    product_id = db.Column(db.Integer, db.ForeignKey('products.id'), nullable=False)  # 确保字段名称是 product_id


# 使用应用上下文
with app.app_context():
    db.create_all()

# 主页面
@app.route('/')
def index():
    return send_from_directory(app.static_folder, 'index.html')

# 用户注册
@app.route('/register', methods=['POST'])
def register():
    data = request.get_json()
    phone_number = data.get('phone_number')
    password = data.get('password')
    role = data.get('role', 'consumer')

    if not phone_number or not password:
        return jsonify({'success': False, 'message': '手机号和密码不能为空'}), 400

    existing_user = User.query.filter_by(phone_number=phone_number).first()
    if existing_user:
        return jsonify({'success': False, 'message': '手机号已存在'})

    hashed_password = generate_password_hash(password)
    new_user = User(phone_number=phone_number, password=hashed_password, role=role)
    db.session.add(new_user)
    db.session.commit()

    return jsonify({'success': True, 'message': '用户注册成功'})

# 用户登录路由（增加保持登录的功能）
@app.route('/login', methods=['POST'])
def login():
    data = request.get_json()
    phone_number = data.get('phone')
    password = data.get('password')

    user = User.query.filter_by(phone_number=phone_number).first()
    if user and check_password_hash(user.password, password):
        # 使用 session 存储用户信息
        session['user_phone'] = user.phone_number
        session['user_id'] = user.id
        session['user_role'] = user.role

        # 创建响应并设置 Cookie
        response = make_response(jsonify({'success': True, 'role': user.role, 'message': '登录成功'}))
        response.set_cookie('user_phone', user.phone_number, max_age=7 * 24 * 60 * 60)  # 设置 cookie 保存 7 天
        response.set_cookie('user_id', str(user.id), max_age=7 * 24 * 60 * 60)
        response.set_cookie('user_role', user.role, max_age=7 * 24 * 60 * 60)
        return response

    return jsonify({'success': False, 'message': '手机号或密码错误'}), 401


# 检查用户是否已登录
@app.route('/check-login', methods=['GET'])
def check_login():
    # 从 Cookie 获取用户信息
    user_phone = request.cookies.get('user_phone')
    user_id = request.cookies.get('user_id')
    user_role = request.cookies.get('user_role')

    if user_phone and user_id and user_role:
        return jsonify({'success': True, 'user_phone': user_phone, 'user_id': user_id, 'user_role': user_role})

    return jsonify({'success': False, 'message': '用户未登录'}), 401



# 用户登出路由
@app.route('/api/logout', methods=['POST'])
def logout():
    # 清除 session 和 cookie
    session.clear()
    response = make_response(jsonify({'success': True, 'message': '已登出'}))
    response.delete_cookie('user_phone')
    response.delete_cookie('user_id')
    response.delete_cookie('user_role')
    return jsonify({'success': True, 'message': '已成功登出'})


# 获取用户信息的路由
@app.route('/api/user_info', methods=['GET'])
def api_user_info():
    user_id = session.get('user_id')
    if not user_id:
        return jsonify({'success': False, 'message': '用户未登录'}), 401

    user = User.query.get(user_id)
    if user:
        return jsonify({'success': True, 'phone_number': user.phone_number, 'role': user.role})
    return jsonify({'success': False, 'message': '用户信息获取失败'}), 404

#管理员界面
# 确保上传文件夹存在
UPLOAD_FOLDER = 'frontend/images/products'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)

# 添加菜品路由
@app.route('/api/products', methods=['POST'])
def add_product():
    if 'file' not in request.files:
        return jsonify({'success': False, 'message': 'No file part'})
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'success': False, 'message': 'No selected file'})
    
    if file:
        # 为图片生成唯一的文件名
        filename = secure_filename(f"{Product.query.count() + 1}{os.path.splitext(file.filename)[1]}")
        file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        file.save(file_path)
        
        # 获取其他字段
        name = request.form['name']
        description = request.form['description']
        price = request.form['price']
        stock = request.form['stock']
        window_id = request.form['window_id']
        
        # 存储菜品信息到数据库
        new_product = Product(name=name, description=description, picture_path=filename, price=price, stock=stock, window_id=window_id)
        db.session.add(new_product)
        db.session.commit()
        
        return jsonify({'success': True, 'message': 'Product added successfully'})
    
# 获取单个商品信息用于编辑的路由
@app.route('/api/products/<int:product_id>/edit', methods=['GET'])
def get_product(product_id):
    product = Product.query.get(product_id)  # 直接使用位置参数
    if product:
        return jsonify({
            'success': True,
            'data': {
                'id': product.id,
                'name': product.name,
                'description': product.description,
                'price': product.price,
                'stock': product.stock,
                'window_id': product.window_id
            }
        })
    else:
        return jsonify({'success': False, 'message': 'Product not found'}), 404

# 更新商品信息的路由
@app.route('/api/products/<int:product_id>', methods=['PUT'])
def update_product(product_id):
    data = request.get_json()
    product = Product.query.get(product_id)  # 直接使用位置参数
    if product:
        product.name = data.get('name', product.name)
        product.description = data.get('description', product.description)
        product.price = data.get('price', product.price)
        product.stock = data.get('stock', product.stock)
        product.window_id = data.get('window_id', product.window_id)
        db.session.commit()
        return jsonify({'success': True, 'message': 'Product updated successfully'})
    else:
        return jsonify({'success': False, 'message': 'Product not found'}), 404


# 删除商品
@app.route('/api/products/<int:products_id>', methods=['DELETE'])
def delete_product(products_id):
    product = Product.query.get(products_id)
    if not product:
        return jsonify({'success': False, 'message': '商品未找到'}), 404
    db.session.delete(product)
    db.session.commit()
    return jsonify({'success': True, 'message': '商品已删除'})


#管理员界面路由
# 增加窗口
@app.route('/api/windows', methods=['POST'])
def add_window():
    data = request.get_json()
    name = data.get('name')
    if not name:
        return jsonify({'success': False, 'message': '窗口名称不能为空'}), 400

    new_window = Window(name=name)
    db.session.add(new_window)
    db.session.commit()

    return jsonify({'success': True, 'message': '窗口添加成功', 'id': new_window.id})
@app.route('/api/windows', methods=['GET'])
def get_windows():
    # 查询数据库中的所有窗口信息
    windows = Window.query.all()
    
    # 将窗口信息转换为字典列表，以便 jsonify 能够序列化
    window_list = [{'id': window.id, 'name': window.name} for window in windows]
    # 返回 JSON 格式的数据
    return jsonify({'windows': window_list})

@app.route('/api/windows/<int:window_id>/edit', methods=['GET'])
def edit_window(window_id):
    # 获取窗口详情的逻辑
    window = Window.query.get(window_id)
    if not window:
        return jsonify({'success': False, 'message': '窗口未找到'}), 404
    return jsonify({'success': True, 'id': window.id, 'name': window.name})

@app.route('/api/windows/<int:window_id>', methods=['PUT'])
def update_window(window_id):
    # 获取请求体中的数据
    data = request.get_json()
    new_name = data.get('name')

    # 检查新名称是否提供
    if not new_name:
        return jsonify({'success': False, 'message': '窗口名称不能为空'}), 400

    # 根据窗口ID获取窗口对象
    window = Window.query.get(window_id)
    if not window:
        return jsonify({'success': False, 'message': '窗口未找到'}), 404

    # 更新窗口名称
    window.name = new_name
    db.session.commit()
    return jsonify({'success': True, 'message': '窗口名称更新成功'})

@app.route('/api/windows/<int:window_id>', methods=['DELETE'])
def delete_window(window_id):
    # 删除窗口的逻辑
    window = Window.query.get(window_id)
    if not window:
        return jsonify({'success': False, 'message': '窗口未找到'}), 404
    db.session.delete(window)
    db.session.commit()
    return jsonify({'success': True, 'message': '窗口已删除'})


@app.route('/api/orders', methods=['GET'])
def get_user_orders():
    # 获取当前用户 ID
    user_id = session.get('user_id')

    if not user_id:
        return jsonify({'success': False, 'message': '用户未登录'}), 401

    # 获取该用户的所有订单信息
    orders = db.session.query(Orders).filter_by(consumer_id=user_id).all()

    orders_list = []
    for order in orders:
        # 获取订单详情
        order_items = db.session.query(OrderItem).filter_by(order_id=order.id).all()

        order_data = {
            'id': order.id,
            'total_price': str(order.total_price),
            'status': order.status,
            'created_at': order.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'items': [
                {
                    'product_id': item.products_id,
                    'product_name': item.product.name,
                    'quantity': item.quantity,
                    'price': str(item.price)
                }
                for item in order_items
            ]
        }
        orders_list.append(order_data)

    return jsonify(orders_list)



@app.route('/api/feedback', methods=['POST'])
def submit_feedback():
    data = request.get_json()
    order_id = data.get('order_id')
    rating = data.get('rating')
    comment = data.get('comment')
    consumer_id = session.get('user_id')

    if not all([order_id, rating, comment, consumer_id]):
        return jsonify({'success': False, 'message': '缺少必要参数'}), 400

    # 查询order_items表以获取product_id
    product_id = None
    try:
        product_id = db.session.query(OrderItem.products_id).filter_by(order_id=order_id).first()
        if product_id:
            product_id = product_id[0]  # 获取product_id的值
        else:
            return jsonify({'success': False, 'message': '未找到对应的订单项'}), 404
    except Exception as e:
        db.session.rollback()
        return jsonify({'success': False, 'message': '数据库查询失败', 'error': str(e)}), 500

    feedback = Feedback(
        consumer_id=consumer_id,
        order_id=order_id,
        product_id=product_id,
        rating=int(rating),
        comment=comment,
        created_at=datetime.now()
    )
    try:
        db.session.add(feedback)
        db.session.commit()
        return jsonify({'success': True, 'message': '评价提交成功'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'success': False, 'message': '提交评价失败', 'error': str(e)}), 500

# 获取所有订单及其详情的路由
@app.route('/api/admin/orders', methods=['GET'])
def get_orders():
    orders = Orders.query.all()
    order_details = []  # 初始化一个空列表用于存储订单详情
    for order in orders:
        order_items = OrderItem.query.filter_by(order_id=order.id).all()  # 获取订单项
        order_detail = {
            'id': order.id,
            'consumer_id': order.consumer_id,
            'total_price': order.total_price,
            'status': order.status,
            'created_at': order.created_at,
            'items': []
        }
        
        # 获取每个订单项的菜品名称
        for item in order_items:
            product = Product.query.get(item.products_id)  # 根据产品ID获取菜品名称
            if product:
                order_detail['items'].append({
                    'product_name': product.name,  # 使用菜品名称替换产品ID
                    'quantity': item.quantity,
                    'price': item.price
                })
        
        order_details.append(order_detail)  # 将订单详情添加到列表
    return jsonify({'orders': order_details})


@app.route('/api/admin/orders/<int:order_id>/complete', methods=['POST'])
def complete_order(order_id):
    # 检查用户是否为管理员
    user_role = session.get('user_role')
    if user_role != 'admin':
        return jsonify({'success': False, 'message': '权限不足'}), 403

    # 获取订单并更新状态
    order = Orders.query.get(order_id)
    if not order:
        return jsonify({'success': False, 'message': '订单未找到'}), 404

    # 减少库存
    if order.status == 'pending':
        order_items = OrderItem.query.filter_by(order_id=order_id).all()
        for item in order_items:
            product = Product.query.get(item.products_id)
            if product and product.stock >= item.quantity:
                product.stock -= item.quantity
                db.session.commit()
            else:
                return jsonify({'success': False, 'message': '库存不足，无法完成订单'}), 400

        # 更新订单状态为 'completed'
        order.status = 'completed'
        db.session.commit()
        return jsonify({'success': True, 'message': '订单状态已更新为完成，库存已减少'})
    else:
        return jsonify({'success': False, 'message': '订单已完成或已取消，无法再次完成'}), 400










#用户界面
# 获取所有菜品
@app.route('/api/products', methods=['GET'])
def api_get_products():
    products = Product.query.all()
    output = []
    for product in products:
        product_data = {
            'id': product.id,
            'name': product.name,
            'description': product.description,
            'price': str(product.price),
            'stock': product.stock,
            'picture_path': product.picture_path,
            'window_id': product.window_id,
            'window_name': product.window.name
        }
        output.append(product_data)
    return jsonify({'products': output})

@app.route('/api/window_queue_count/<int:window_id>', methods=['GET'])
def get_window_queue_count(window_id):
    # 查询该窗口排队的订单数，状态为 'pending' 的订单
    queue_count = db.session.query(OrderItem).join(Orders).filter(
        OrderItem.window_id == window_id,
        Orders.status == 'pending'
    ).count()
    
    return jsonify({'window_id': window_id, 'queue_count': queue_count})


# 获取购物车中的商品
@app.route('/api/cart', methods=['GET'])
def get_cart_items():
    user_id = session.get('user_id')
    if not user_id:
        return jsonify({'success': False, 'message': '用户未登录'})
    
    cart_items = Cart.query.filter_by(user_id=user_id).all()
    # 格式化返回数据
    cart_items_data = []
    for item in cart_items:
        cart_items_data.append({
            'id': item.id,
            'quantity': item.quantity,
            'product': {
                'name': item.product.name,
                'price': item.product.price,
                'picture_path': item.product.picture_path  # Ensure this field exists
            }
        })
    
    return jsonify({'cartItems': cart_items_data})


# 获取商品评论并返回平均评分
@app.route('/api/products/<int:product_id>/comments', methods=['GET'])
def get_product_comments(product_id):
    feedbacks = Feedback.query.filter_by(product_id=product_id).all()
    comments = []
    total_rating = 0
    for feedback in feedbacks:
        comments.append({
            'rating': feedback.rating,
            'comment': feedback.comment
        })
        total_rating += feedback.rating

    average_rating = total_rating / len(feedbacks) if feedbacks else 0

    return jsonify({
        'product': {'name': 'Product Name', 'description': 'Product Description', 'price': 10.0},  # 产品信息
        'comments': comments,
        'average_rating': average_rating
    })




# 向购物车添加商品
@app.route('/api/cart', methods=['POST'])
def add_to_cart():
    user_id = session.get('user_id')
    if not user_id:
        return jsonify({'success': False, 'message': '用户未登录'}), 401

    data = request.get_json()
    product_id = data.get('product_id')
    quantity = data.get('quantity')

    product = Product.query.get(product_id)
    if not product:
        return jsonify({'success': False, 'message': '商品未找到'}), 404

    # 检查购物车是否已有该商品
    cart_item = Cart.query.filter_by(user_id=user_id, products_id=product_id).first()
    if cart_item:
        # 如果商品已在购物车中，更新数量
        cart_item.quantity += quantity
    else:
        # 如果商品不在购物车中，创建新的购物车项
        cart_item = Cart(user_id=user_id, products_id=product_id, quantity=quantity)
        db.session.add(cart_item)

    db.session.commit()
    return jsonify({'success': True, 'message': '商品已添加到购物车'})

# 删除购物车中的商品
@app.route('/api/cart/<int:cart_item_id>', methods=['DELETE'])
def remove_from_cart(cart_item_id):
    user_id = session.get('user_id')
    if not user_id:
        return jsonify({'success': False, 'message': '用户未登录'}), 401

    cart_item = Cart.query.get(cart_item_id)
    if not cart_item or cart_item.user_id != user_id:
        return jsonify({'success': False, 'message': '购物车项未找到'}), 404

    db.session.delete(cart_item)
    db.session.commit()
    return jsonify({'success': True, 'message': '商品已从购物车中删除'})

# 更新购物车中商品的数量
@app.route('/api/cart/<int:cart_item_id>', methods=['PUT'])
def update_cart_item(cart_item_id):
    user_id = session.get('user_id')
    if not user_id:
        return jsonify({'success': False, 'message': '用户未登录'}), 401

    data = request.get_json()
    quantity = data.get('quantity')

    cart_item = Cart.query.get(cart_item_id)
    if not cart_item or cart_item.user_id != user_id:
        return jsonify({'success': False, 'message': '购物车项未找到'}), 404

    # 更新数量
    if quantity <= 0:
        return jsonify({'success': False, 'message': '数量必须大于0'}), 400

    cart_item.quantity = quantity
    db.session.commit()
    return jsonify({'success': True, 'message': '购物车商品数量已更新'})



# 提交订单
@app.route('/api/checkout', methods=['POST'])
def checkout():
    user_id = session.get('user_id')
    if not user_id:
        return jsonify({'success': False, 'message': '用户未登录'}), 401

    # 获取用户的购物车商品
    cart_items = Cart.query.filter_by(user_id=user_id).all()
    if not cart_items:
        return jsonify({'success': False, 'message': '购物车为空，无法提交订单'}), 400

    # 计算订单的总金额
    total_price = 0
    for item in cart_items:
        total_price += item.product.price * item.quantity

    # 创建订单，不传递 consumer_location 字段
    order = Orders(
        consumer_id=user_id,
        total_price=total_price,
        status='pending',  # 设置订单状态为 'pending'
    )
    db.session.add(order)
    db.session.commit()  # 提交事务以生成订单 ID

    # 创建订单项
    for item in cart_items:
        order_item = OrderItem(
            order_id=order.id,
            products_id=item.products_id,
            quantity=item.quantity,
            price=item.product.price,
            window_id=item.product.window_id
        )
        db.session.add(order_item)

    db.session.commit()

    # 清空购物车
    for item in cart_items:
        db.session.delete(item)
    
    db.session.commit()

    # 返回成功信息
    return jsonify({
        'success': True,
        'message': '订单已提交',
        'order_id': order.id,
        'total_price': total_price
    })

@app.route('/api/rank/sales', methods=['GET'])
def get_sales_rank():
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 5))

    # 查询并按销量降序排序，同时计算评分
    query = text("""
        SELECT p.id, p.name, p.price, p.stock, p.picture_path, 
               SUM(oi.quantity) AS total_sales, AVG(f.rating) AS average_rating
        FROM products p
        LEFT JOIN order_items oi ON p.id = oi.products_id
        LEFT JOIN feedback f ON p.id = f.product_id
        GROUP BY p.id
        ORDER BY total_sales DESC
    """)

    products = db.session.execute(query).fetchall()

    # 分页
    start = (page - 1) * per_page
    end = start + per_page
    paginated_products = products[start:end]

    # 返回数据
    return jsonify({
        'page': page,
        'per_page': per_page,
        'total_pages': ceil(len(products) / per_page),
        'products': [
            {
                'id': p[0],
                'name': p[1],
                'price': p[2],
                'stock': p[3],
                'picture_path': p[4],
                'total_sales': p[5],
                'average_rating': round(p[6], 1) if p[6] is not None else '暂无评分'
            } for p in paginated_products
        ]
    })



@app.route('/api/rank/price', methods=['GET'])
def get_price_rank():
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 5))

    # 按价格降序排序，同时计算销量和评分
    query = text("""
        SELECT p.id, p.name, p.price, p.stock, p.picture_path, 
               SUM(oi.quantity) AS total_sales, AVG(f.rating) AS average_rating
        FROM products p
        LEFT JOIN order_items oi ON p.id = oi.products_id
        LEFT JOIN feedback f ON p.id = f.product_id
        GROUP BY p.id
        ORDER BY p.price DESC
    """)

    result = db.session.execute(query).fetchall()

    # 分页处理
    start = (page - 1) * per_page
    end = start + per_page
    paginated_products = result[start:end]

    return jsonify({
        'page': page,
        'per_page': per_page,
        'total_pages': ceil(len(result) / per_page),
        'products': [
            {
                'id': p[0],
                'name': p[1],
                'price': p[2],
                'stock': p[3],
                'picture_path': p[4],
                'total_sales': p[5] if p[5] is not None else 0,
                'average_rating': round(p[6], 1) if p[6] is not None else '暂无评分'
            } for p in paginated_products
        ]
    })




@app.route('/api/rank/quantity', methods=['GET'])
def get_quantity_rank():
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 5))

    # 按库存降序排序，同时计算销量和评分
    query = text("""
        SELECT p.id, p.name, p.price, p.stock, p.picture_path, 
               SUM(oi.quantity) AS total_sales, AVG(f.rating) AS average_rating
        FROM products p
        LEFT JOIN order_items oi ON p.id = oi.products_id
        LEFT JOIN feedback f ON p.id = f.product_id
        GROUP BY p.id
        ORDER BY p.stock DESC
    """)

    result = db.session.execute(query).fetchall()

    # 分页处理
    start = (page - 1) * per_page
    end = start + per_page
    paginated_products = result[start:end]

    return jsonify({
        'page': page,
        'per_page': per_page,
        'total_pages': ceil(len(result) / per_page),
        'products': [
            {
                'id': p[0],
                'name': p[1],
                'price': p[2],
                'stock': p[3],
                'picture_path': p[4],
                'total_sales': p[5] if p[5] is not None else 0,
                'average_rating': round(p[6], 1) if p[6] is not None else '暂无评分'
            } for p in paginated_products
        ]
    })




from sqlalchemy import func

@app.route('/api/rank/rating', methods=['GET'])
def get_rating_rank():
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 5))

    # 按评分降序排序，同时计算销量
    query = (
        db.session.query(
            Product.id,
            Product.name,
            Product.price,
            Product.stock,
            Product.picture_path,
            func.sum(OrderItem.quantity).label('total_sales'),  # 计算销量
            func.avg(Feedback.rating).label('average_rating')  # 计算评分
        )
        .outerjoin(OrderItem, OrderItem.products_id == Product.id)  # 外连接订单项
        .outerjoin(Feedback, Feedback.product_id == Product.id)  # 外连接反馈
        .group_by(Product.id)  # 按商品分组
        .order_by(func.avg(Feedback.rating).desc())  # 按评分降序
    )

    result = query.all()

    # 分页处理
    start = (page - 1) * per_page
    end = start + per_page
    paginated_products = result[start:end]

    return jsonify({
        'page': page,
        'per_page': per_page,
        'total_pages': ceil(len(result) / per_page),
        'products': [
            {
                'id': p[0],
                'name': p[1],
                'price': p[2],
                'stock': p[3],
                'picture_path': p[4],
                'total_sales': p[5] if p[5] is not None else 0,
                'average_rating': round(p[6], 1) if p[6] is not None else '暂无评分'
            } for p in paginated_products
        ]
    })





# 催单的路由
@app.route('/api/urge-order', methods=['POST'])
def urge_order():
    data = request.get_json()
    order_id = data.get('order_id')

    if not order_id:
        return jsonify({'success': False, 'message': '缺少订单ID'}), 400

    # 根据订单ID获取订单对象
    order = Orders.query.get(order_id)
    if not order:
        return jsonify({'success': False, 'message': '订单未找到'}), 404

    # 检查订单状态是否为待完成
    if order.status != 'pending':
        return jsonify({'success': False, 'message': '订单已完成或已取消，无法催单'}), 400

    # 这里添加催单的逻辑，例如更新订单状态或通知后厨
    # 假设催单操作成功
    # order.status = 'urged'  # 示例：更新订单状态为 'urged'
    # db.session.commit()

    return jsonify({'success': True, 'message': '催单成功'})

# 确保在 main.py 文件的最后调用 if __name__ == '__main__': 来运行 app
if __name__ == '__main__':
    app.run(host="0.0.0.0", debug=True)
